﻿@page "/Form8"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form8> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总, 业务经理, 业务员")]

<HeadContent>
    <style>
        .multi-select {
            --bb-multi-select-min-height: 26px;
        }

    </style>
    

</HeadContent>


<div style="width: 200px;">
</div>

<div class="row g-1">
    <div class="col-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>

        <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="门店" />
            <MultiSelect Items="@客商门店对照Items" @bind-Value="@选中门店List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
            </BootstrapInputGroup>
        </div>
</div>
    <div class="row g-1">
    <div class="col-9 col-sm-9">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" Class="custom-radio8"/>
    </div>
    <div class="col-3 col-sm-6 col-md-6 col-lg-auto">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>
     </div>
</div>
@* <div class="table-fixed-header"> *@
    <Table class="table" TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
           IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="false"
           OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" IsStriped="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact"
           Height="500" IsFixedHeader="true">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else if (property.ColumnName == "序号")
                        {
                            @:合计
                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>
@* </div> *@
@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<T客商门店对照> 客商门店对照List = new List<T客商门店对照>();
    //时间范围
    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 客商门店对照Items => 客商门店对照List.Select(i => new SelectedItem(i.posName!, i.posName! +"--" +i.U8name!)).ToList();
    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "门店-客户名称", Value = "0" },
        new SelectedItem { Text = "门店-产品类别", Value = "1"},
        new SelectedItem { Text = "门店-客户数量", Value = "2"}
    });


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中门店List { get; set; }
    private string 选中门店InStr = "";
    private string 选中查询类别 { get; set; }

    //表格数据
    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }

    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });

        客商门店对照List = _SqlSugarScope.Queryable<T客商门店对照>().OrderBy(it => it.U8客户编码).ToList();
        // //加一个请选择
        // View_DepartList.Insert(0, new View_Depart { cDepCode = "", cDepName = "请选择" });

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中门店List != null)
        {
            foreach (var item in 选中门店List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中门店InStr = string.Join("','", items.Select(it => it.Value).ToArray());
        return Task.CompletedTask;
    }


    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"原06代理业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }
    private async void Button_daochu(MouseEventArgs e)
    {
        //复制一个table用来导出
        DataTable dt = DataTableDynamicContext.DataTable.Copy();
        //增加一行合计
        DataRow dr = dt.NewRow();
        if (dt.Columns.Contains("销售额") == true)
        {
            dr["销售额"] = dt.AsEnumerable().Sum(dr =>
                               {
                                   if (dr.IsNull("销售额")) return 0m;
                                   object value = dr["销售额"];
                                   if (value is int)
                                       return (decimal)(int)value;
                                   else if (value is decimal)
                                       return (decimal)value;
                                   else
                                       return 0m;
                               });
        }

        if (dt.Columns.Contains("消费次数") == true)
        {
            dr["消费次数"] = dt.AsEnumerable().Sum(dr =>
                               {
                                   if (dr.IsNull("消费次数")) return 0m;
                                   object value = dr["消费次数"];
                                   if (value is int)
                                       return (decimal)(int)value;
                                   else if (value is decimal)
                                       return (decimal)value;
                                   else
                                       return 0m;
                               });
        }

        if (dt.Columns.Contains("客户数") == true)
        {
            dr["客户数"] = dt.AsEnumerable().Sum(dr =>
                               {
                                   if (dr.IsNull("客户数")) return 0m;
                                   object value = dr["客户数"];
                                   if (value is int)
                                       return (decimal)(int)value;
                                   else if (value is decimal)
                                       return (decimal)value;
                                   else
                                       return 0m;
                               });
        }

        dt.Rows.Add(dr);
        DataTableDynamicContext dataTable = new DataTableDynamicContext(dt);
        var ret = await TableExport.ExportExcelAsync(dataTable.GetItems(), DataTableDynamicContext.GetColumns(), $"门店客户分析表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 导出符合条件的所有数据 data
        // var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns(), $"直营业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        ToastService.Success("提示", "导出成功");
    }
    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        for (int i = 0; i < sortTable.Rows.Count; i++)
        {
            sortTable.Rows[i]["序号"] = i + 1;
        }
        DataTableDynamicContext = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
            });
        StateHasChanged();
        return sortString;
    }



    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;
        // string loginuser = username;
        string loginuser = "admin";
        if (string.IsNullOrEmpty(选中门店InStr) == true)
        {
            选中门店InStr += string.Join("','", 客商门店对照List.Select(it => it.posName));
        }
        string 门店清单 = "('" + 选中门店InStr + "')";
        string guid = Guid.NewGuid().ToString();  ///本次查询统一的

        switch (选中查询类别)
        {
            case "0":
                {
                    sql = "delete T直营业务数据 where usercode='" + loginuser + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    #region 开始塞入
                    sql = @"insert into  T直营业务数据 (guid,period, usercode, dDate, storeid,cInvCode,sheet_no,comflag, 类别,产品名称,  销售额, 消费次数)";
                    sql += "select '" + guid + "','今年','" + loginuser + "',oper_date, storeid ,item_code,sheet_no,comflag,classname,item_name,real_amt,1";
                    sql += " from View_yh_jxc_flow where 1=1 and oper_date>='" + 今年开始日期 + "'  and oper_date<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = @"insert into  T直营业务数据 (guid,period, usercode, dDate, storeid,cInvCode,sheet_no,comflag, 类别,产品名称,  同期销售额, 消费次数)";
                    sql += "select '" + guid + "','去年','" + loginuser + "',oper_date, storeid ,item_code,sheet_no,comflag,classname,item_name,real_amt,1";
                    sql += " from View_yh_jxc_flow where 1=1 and oper_date>='" + 去年开始日期 + "'  and oper_date<='" + 去年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    #endregion
                    #region 更新门店代理商
                    sql = "update T直营业务数据 set no =right(sheet_no,10) where guid='" + guid + "' and len(sheet_no)>=10 ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    sql = "update a set a.门店代理商=b.branchname, a.门店类型=b.branchtype" +
                        " from  T直营业务数据 a , View_yh_ba_branch_info b   where a.guid='" + guid + "'   ";
                    sql += " and a.storeid=b.storeid";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.cCusCode=b.U8客户编码, a.U8name=b.U8name " +
    " from  T直营业务数据 a ,  T客商门店对照 b   where a.guid='" + guid + "'   ";
                    sql += " and a.门店代理商=b.posName";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.客户姓名=c.person_name " +
            " from  T直营业务数据 a , View_yh_pos_bill_master b,View_yh_vip_card_info c   where a.guid='" + guid + "'   ";
                    sql += " and a.no=right(b.flowno,10) and b.vip_no=c.card_id";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    sql = "update T直营业务数据 set 客户姓名='散户' where guid='" + guid + "'  and (客户姓名 is null or 客户姓名='') ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion



                    sql = "SELECT    门店代理商,  客户姓名, Convert(decimal(18,4),sum(isnull(销售额,0))/10000) 销售额,Convert(decimal(18,4),sum(isnull(同期销售额,0))/10000) 同期销售额,Convert(decimal(18,4),(sum(isnull(销售额,0))-sum(isnull(同期销售额,0)))/10000) 销售额差异, sum(消费次数) 消费次数,sum(同期消费次数) 同期消费次数 ,sum(消费次数)-sum(同期消费次数) 消费次数差异  FROM ( ";

                    sql += "SELECT    门店代理商,  客户姓名, isnull(销售额,0) 销售额,0.00 同期销售额, 1 消费次数,0.00 同期消费次数  FROM      T直营业务数据 ";
                    sql += " where  guid='" + guid + "' and period = '今年' and isnull(销售额,0)>0  and 门店代理商 in " + 门店清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    }
                    // sql += " group by 门店代理商,  客户姓名 having  sum(isnull(销售额,0))<>0 ";
                    // sql += " order by 门店代理商,  客户姓名";

                    sql += " union all ";

                    sql += " SELECT    门店代理商,  客户姓名,0.00 销售额, isnull(同期销售额,0) 同期销售额, 0.00 消费次数,1 同期消费次数  FROM      T直营业务数据 ";
                    sql += " where  guid='" + guid + "' and period = '去年' and isnull(同期销售额,0)>0 and 门店代理商 in " + 门店清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    }
                    // sql += " group by 门店代理商,  客户姓名 having  sum(isnull(销售额,0))<>0 ";
                    // sql += " order by 门店代理商,  客户姓名";

                    sql += ")t group by 门店代理商,  客户姓名 order by 门店代理商,  客户姓名";

                    DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);
                    sql = "delete T直营业务数据 where  guid='" + guid + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    if (dataTableHZ != null && dataTableHZ.Rows.Count > 0)
                    {
                        if (dataTableHZ.Columns.Contains("序号") == false)
                        {
                            dataTableHZ.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                        }


                        for (int i = 0; i < dataTableHZ.Rows.Count; i++)
                        {
                            dataTableHZ.Rows[i]["序号"] = i + 1;
                        }
                    }

                    DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ, (context, col) =>
                         {
                             //所有列可排序
                             col.Sortable = true;
                             col.DefaultSort = true;
                             if (col.GetFieldName() == "序号")
                             {
                                 col.Width = 40;
                             }
                         });
                    break;
                }
            case "1":
                {
                    sql = "delete T直营业务数据 where usercode='" + loginuser + "'";

                    #region 开始塞入
                    sql = @"insert into  T直营业务数据 (guid,period, usercode, dDate, storeid,cInvCode,sheet_no,comflag, 类别,产品名称,  销售额, 消费次数)";
                    sql += "select '" + guid + "','今年','" + loginuser + "',oper_date, storeid ,item_code,sheet_no,comflag,classname,item_name,real_amt,1";
                    sql += " from View_yh_jxc_flow where 1=1 and oper_date>='" + 今年开始日期 + "'  and oper_date<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    #endregion
                    #region 更新门店代理商
                    sql = "update T直营业务数据 set no =right(sheet_no,10) where guid='" + guid + "' and len(sheet_no)>=10 ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    sql = "update a set a.门店代理商=b.branchname, a.门店类型=b.branchtype" +
                        " from  T直营业务数据 a , View_yh_ba_branch_info b   where a.guid='" + guid + "'   ";
                    sql += " and a.storeid=b.storeid";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.cCusCode=b.U8客户编码, a.U8name=b.U8name " +
    " from  T直营业务数据 a ,  T客商门店对照 b   where a.guid='" + guid + "'   ";
                    sql += " and a.门店代理商=b.posName";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.客户姓名=c.person_name " +
            " from  T直营业务数据 a , View_yh_pos_bill_master b,View_yh_vip_card_info c   where a.guid='" + guid + "'   ";
                    sql += " and a.no=right(b.flowno,10) and b.vip_no=c.card_id";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    sql = "update T直营业务数据 set 客户姓名='散户' where guid='" + guid + "'  and (客户姓名 is null or 客户姓名='') ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    sql = "SELECT   门店代理商,  客户姓名,类别,产品名称, Convert(decimal(18,4),sum(销售额)/10000) 销售额   FROM      T直营业务数据 ";
                    sql += " where  guid='" + guid + "' and 门店代理商 in " + 门店清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    }
                    sql += " group by 门店代理商,  客户姓名,类别,产品名称 having  sum(销售额)<>0 ";
                    sql += " order by 门店代理商,  客户姓名,类别,产品名称 ";
                    DataTable dataTableHZ2 = _SqlSugarScope.Ado.GetDataTable(sql);
                    sql = "delete T直营业务数据 where  guid='" + guid + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    if (dataTableHZ2 != null && dataTableHZ2.Rows.Count> 0)
                    {
                        if (dataTableHZ2.Columns.Contains("序号") == false)
                        {
                            dataTableHZ2.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                        }

                        for (int i = 0; i < dataTableHZ2.Rows.Count; i++)
                        {
                            dataTableHZ2.Rows[i]["序号"] = i + 1;
                        }
                    }
                    DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ2, (context, col) =>
                         {
                             if (col.GetFieldName() == "序号")
                             {
                                 col.Width = 40;
                             }
                             //所有列可排序
                             col.Sortable = true;
                             col.DefaultSort = true;
                         });
                    break;
                }
            default:
                {

                    #region 开始塞入
                    sql = @"insert into  T直营业务数据 (guid,period, usercode, dDate, storeid,cInvCode,sheet_no,comflag, 类别,产品名称,  销售额)";
                    sql += "select '" + guid + "','今年','" + loginuser + "',oper_date, storeid ,item_code,sheet_no,comflag,classname,item_name,real_amt";
                    sql += " from View_yh_jxc_flow where 1=1 and oper_date>='" + 今年开始日期 + "'  and oper_date<='" + 今年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = @"insert into  T直营业务数据 (guid,period, usercode, dDate, storeid,cInvCode,sheet_no,comflag, 类别,产品名称,  同期销售额)";
                    sql += "select '" + guid + "','去年','" + loginuser + "',oper_date, storeid ,item_code,sheet_no,comflag,classname,item_name,real_amt";
                    sql += " from View_yh_jxc_flow where 1=1 and oper_date>='" + 去年开始日期 + "'  and oper_date<='" + 去年结束日期 + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    #region 更新门店代理商
                    sql = "update T直营业务数据 set no =right(sheet_no,10) where guid='" + guid + "' and len(sheet_no)>=10 ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    sql = "update a set a.门店代理商=b.branchname, a.门店类型=b.branchtype" +
                        " from  T直营业务数据 a , View_yh_ba_branch_info b   where a.guid='" + guid + "'   ";
                    sql += " and a.storeid=b.storeid";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.经营模式=b.经营模式  " +
" from  T直营业务数据 a ,  T经营模式 b   where a.guid='" + guid + "'   ";
                    sql += " and a.门店类型=b.门店类型";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);


                    sql = "update a set a.cCusCode=b.U8客户编码, a.U8name=b.U8name " +
    " from  T直营业务数据 a ,  T客商门店对照 b   where a.guid='" + guid + "'   ";
                    sql += " and a.门店代理商=b.posName";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    sql = "update a set a.客户姓名=c.person_name " +
            " from  T直营业务数据 a , View_yh_pos_bill_master b,View_yh_vip_card_info c   where a.guid='" + guid + "'   ";
                    sql += " and a.no=right(b.flowno,10) and b.vip_no=c.card_id";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    sql = "update T直营业务数据 set 客户姓名='散户' where guid='" + guid + "' and (客户姓名 is null or 客户姓名='') ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);
                    #endregion
                    // sql = "SELECT    门店代理商, 经营模式, Convert(decimal(18,4),sum(销售额)/10000) 销售额, '' 客户数   FROM      T直营业务数据 ";
                    // sql += " where  guid='" + guid + "' and 门店代理商 in " + 门店清单;
                    // if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    // {
                    //     sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    // }
                    // sql += " group by 门店代理商, 经营模式 having  sum(销售额)<>0 ";
                    // sql += " order by 门店代理商, 经营模式 ";

                    sql = "SELECT     门店代理商, 经营模式, Convert(decimal(18,4),sum(isnull(销售额,0))/10000) 销售额,Convert(decimal(18,4),sum(isnull(同期销售额,0))/10000) 同期销售额,Convert(decimal(18,4),(sum(isnull(销售额,0))-sum(isnull(同期销售额,0)))/10000) 销售额差异, sum(客户数) 客户数,sum(同期客户数) 同期客户数 ,sum(同期客户数)-sum(同期客户数) 客户数差异  FROM ( ";

                    sql += "SELECT    门店代理商, 经营模式, isnull(销售额,0) 销售额,0.00 同期销售额, 0.0 客户数,0.00 同期客户数  FROM      T直营业务数据 ";
                    sql += " where  guid='" + guid + "' and period = '今年' and isnull(销售额,0)>0  and 门店代理商 in " + 门店清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    }
                    // sql += " group by 门店代理商,  客户姓名 having  sum(isnull(销售额,0))<>0 ";
                    // sql += " order by 门店代理商,  客户姓名";

                    sql += " union all ";

                    sql += " SELECT     门店代理商, 经营模式,0.00 销售额, isnull(同期销售额,0) 同期销售额, 0.00 客户数,0.0 同期客户数  FROM      T直营业务数据 ";
                    sql += " where  guid='" + guid + "' and period = '去年' and isnull(同期销售额,0)>0 and 门店代理商 in " + 门店清单;
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and U8name like '%" + this.选中客户?.Text + "%'";
                    }
                    // sql += " group by 门店代理商,  客户姓名 having  sum(isnull(销售额,0))<>0 ";
                    // sql += " order by 门店代理商,  客户姓名";

                    sql += ")t group by 门店代理商,  经营模式 order by 门店代理商,  经营模式";

                    DataTable dataTableHZ3 = _SqlSugarScope.Ado.GetDataTable(sql);

                    get差异客户数(guid,ref dataTableHZ3);
                    // for (int i = 0; i < dataTableHZ3.Rows.Count; i++)
                    // {

                    //     (int 今年, int 去年, int 差异) 客户数 = get差异客户数(dataTableHZ3.Rows[i]["门店代理商"].ToString(), guid);
                    //     dataTableHZ3.Rows[i]["客户数"] = 客户数.今年;
                    //     dataTableHZ3.Rows[i]["同期客户数"] = 客户数.去年;
                    //     dataTableHZ3.Rows[i]["客户数差异"] = 客户数.差异;
                    // }
                    sql = "delete T直营业务数据 where  guid='" + guid + "'";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    if (dataTableHZ3 != null && dataTableHZ3.Rows.Count > 0)
                    {
                        if (dataTableHZ3.Columns.Contains("序号") == false)
                        {
                            dataTableHZ3.Columns.Add("序号", typeof(int)).SetOrdinal(0);
                        }

                        for (int i = 0; i < dataTableHZ3.Rows.Count; i++)
                        {
                            dataTableHZ3.Rows[i]["序号"] = i + 1;
                        }
                    }

                    DataTableDynamicContext = new DataTableDynamicContext(dataTableHZ3, (context, col) =>
                         { if (col.GetFieldName() == "序号")
                             {
                                 col.Width = 40;
                             }
                             //所有列可排序
                             col.Sortable = true;
                             col.DefaultSort = true;
                         });
                    break;
                }
        }
        await ToastService.Information("提示", "查询成功");

    }
    private string get客户数(string 门店代理商, string guid)
    {
        string sql;
        sql = "select count(1) from ( select distinct 客户姓名 from  T直营业务数据  where  guid='" + guid + "' and 门店代理商='" + 门店代理商 + "') t";
        string 客户数 = _SqlSugarScope.Ado.GetString(sql);
        return 客户数;
    }

    // private void get差异客户数( string guid,ref DataTable dt源)
    // {
    //     int 今年 = 0;
    //     int 去年 = 0;
    //     int 差异 = 0;
    //     string sql;
    //     sql = "select '今年' period,门店代理商,count(1) from ( select distinct 客户姓名 from  T直营业务数据  where  guid='" + guid + "'  and period = '今年') t group by 门店代理商";
    //     sql += " union all ";
    //     sql += "select '去年' period,门店代理商,count(1) from ( select distinct 客户姓名 from  T直营业务数据  where  guid='" + guid + "'  and period = '去年') t group by 门店代理商";
    //     DataTable dt = _SqlSugarScope.Ado.GetDataTable(sql);
    //     if (dt != null && dt.Rows.Count > 0 )
    //     {
    //         for (int i = 0; i < dt源.Rows.Count; i++)
    //         {
    //             string 门店代理商 = dt源.Rows[i]["门店代理商"].ToString();
    //             //根据门店代理商找到今年的客户数，更新到dt源
    //             DataRow[] drs = dt.Select("门店代理商='" + 门店代理商 + "' and period='今年'");
    //             if (drs.Length > 0)
    //             {
    //                 今年 = Convert.ToInt32(drs[0][2]);
    //             }
    //             drs = dt.Select("门店代理商='" + 门店代理商 + "' and period='去年'");
    //             if (drs.Length > 0)
    //             {
    //                 去年 = Convert.ToInt32(drs[0][2]);
    //             }
    //             差异 = 今年 - 去年;

    //             dt源.Rows[i]["客户数"] = 今年;
    //             dt源.Rows[i]["同期客户数"] = 去年;
    //             dt源.Rows[i]["客户数差异"] = 差异;
    //         }

    //     }

    // }

    private void get差异客户数(string guid, ref DataTable dt源)
    {
        // 构建查询 SQL
        string sql = $@"
        SELECT period, 门店代理商, COUNT(DISTINCT 客户姓名) AS 客户数
        FROM T直营业务数据
        WHERE guid = '{guid}' AND period IN ('今年', '去年')
        GROUP BY period, 门店代理商";

        var parameters = new { guid };
        DataTable dt = _SqlSugarScope.Ado.GetDataTable(sql);

        if (dt == null || dt.Rows.Count == 0) return;

        // 将结果转换为字典，以便快速查找
        var customerCounts = new Dictionary<string, (int 今年, int 去年)>();

        foreach (DataRow row in dt.Rows)
        {
            string period = row["period"].ToString();
            string agent = row["门店代理商"].ToString();
            int customerCount = Convert.ToInt32(row["客户数"]);

            if (!customerCounts.ContainsKey(agent))
            {
                customerCounts[agent] = (今年: 0, 去年: 0);
            }

            if (period == "今年")
            {
                customerCounts[agent] = (今年: customerCount, 去年: customerCounts[agent].去年);
            }
            else if (period == "去年")
            {
                customerCounts[agent] = (今年: customerCounts[agent].今年, 去年: customerCount);
            }
        }

        // 更新 dt源 的数据
        foreach (DataRow row in dt源.Rows)
        {
            string agent = row["门店代理商"].ToString();

            if (customerCounts.TryGetValue(agent, out var counts))
            {
                int 今年客户数 = counts.今年;
                int 去年客户数 = counts.去年;
                int 差异 = 今年客户数 - 去年客户数;

                row["客户数"] = 今年客户数;
                row["同期客户数"] = 去年客户数;
                row["客户数差异"] = 差异;
            }
            else
            {
                // 如果没有找到记录，则设置默认值
                row["客户数"] = 0;
                row["同期客户数"] = 0;
                row["客户数差异"] = 0;
            }
        }
    }

}
